﻿using System;
using System.Text;
using System.Data;
using CRMFramework;
using System.Text.RegularExpressions;
using System.Globalization;
using System.Collections.Generic;
using System.IO;
using CRMEntity;
using System.Xml;
using System.Xml.Linq;
using System.Web.UI;
using System.Web;

namespace CRMBiz
{
    public static class ApplicantCreditCheckHandler
    {
        public static DataSet GetClientDetalis(string app_id)
        {
            DataSet ds;
            try
            {
                var para = new[] {
                    DBhelper.SetParameter("@app_id", app_id)
                };
                string strQuery = "SELECT trim(concat(trim(crm_contact.FirstName),' ',trim(crm_contact.MiddleName),' ',trim(crm_contact.SurName))) as ClientName, crm_contact.ContactID FROM crm_contact INNER JOIN crm_applicationcontact ON crm_contact.ContactID = crm_applicationcontact.ContactID INNER JOIN crm_application ON crm_applicationcontact.app_id=crm_application.app_id WHERE crm_application.app_id=@app_id and crm_applicationcontact.deletedFlag=0 and crm_application.deletedFlag=0";
                ds = DBhelper.ExecuteDataset(strQuery, para);
            }
            catch (Exception ex)
            {
                throw ex;
            }
            return ds;
        }

        public static DataSet GetSingleClientDetalis(string ContactID, string app_id)
        {
            DataSet ds;
            try
            {
                var para = new[] {
                    DBhelper.SetParameter("@app_id", app_id),
                    DBhelper.SetParameter("@ContactID", ContactID)
                };
                string strQuery = "select crm_contact.FirstName, crm_contact.SurName, crm_contact.Gender, crm_contact.DriverLicense, crm_contact.DateBirth, crm_contact.StreetNo, crm_contact.Street, crm_contact.Suburb, crm_contact.State, crm_contact.PostCode, crm_application.DesiredAmount as LoanAmount FROM crm_contact INNER JOIN crm_applicationcontact ON crm_contact.ContactID = crm_applicationcontact.ContactID INNER JOIN crm_application ON crm_applicationcontact.app_id=crm_application.app_id where crm_contact.ContactID=@ContactID and crm_application.app_id=@app_id";
                ds = DBhelper.ExecuteDataset(strQuery, para);
            }
            catch (Exception ex)
            {
                throw ex;
            }
            return ds;
        }

        public static DataSet GetSingleVedaCreditCheckHistory(string ContactID)
        {
            DataSet ds;
            try
            {
                var para = new[] {
                    DBhelper.SetParameter("@ContactID", ContactID),
                };
                string strQuery = "select CreatedDate, udf_getformatedDate(CreatedDate) as CreatedDate1, (select trim(concat(trim(FirstName),' ',trim(MiddleName),' ',trim(SurName))) from crm_contact where ContactID=@ContactID) as FullName, XMLFile, VedafileNO, CreatedBy from crm_vedacreditcheckhistory where CurrentContactID=@ContactID order by CreatedDate desc";
                ds = DBhelper.ExecuteDataset(strQuery, para);
            }
            catch (Exception ex)
            {
                throw ex;
            }
            return ds;
        }

        public static void UpdateClientProfile(string app_id, string ContactID, string FirstGivenName, string FamilyName, string Gender, string DriversLicenceNumber, string DateofBirth, string StreetNumber, string StreetName, string Suburb, string State, string PostCode)
        {
            try
            {

                var para = new[] {
                    DBhelper.SetParameter("@ContactID", ContactID),
                    DBhelper.SetParameter("@FirstGivenName", FirstGivenName),
                    DBhelper.SetParameter("@FamilyName", FamilyName),
                    DBhelper.SetParameter("@Gender", Gender),
                    DBhelper.SetParameter("@DriversLicenceNumber", DriversLicenceNumber),
                    DBhelper.SetParameter("@DateofBirth", DateofBirth),
                    DBhelper.SetParameter("@StreetNumber", StreetNumber),
                    DBhelper.SetParameter("@StreetName", StreetName),
                    DBhelper.SetParameter("@Suburb", Suburb),
                    DBhelper.SetParameter("@State", State),
                    DBhelper.SetParameter("@PostCode", PostCode)
                };
                string strQuery = "update crm_contact set FirstName=@FirstGivenName,SurName=@FamilyName,Gender=@Gender,DriverLicense=@DriversLicenceNumber,DateBirth=STR_TO_DATE(@DateofBirth,'%m/%d/%Y'),StreetNo=@StreetNumber,Street=@StreetName,Suburb=@Suburb,State=@State,PostCode=@PostCode where ContactID=@ContactID";
                DBhelper.ExecuteNonQuery(strQuery, para);
            }
            catch (Exception ex)
            {
                throw ex;
            }
        }

        public static int UpdateVedaCreditCheck(string app_id, string ContactID, VedaResponse objVedaResponse, string CreatedDate, string CreatedBy, string XMLFile)
        {
            int CreditcheckhistoryID = 0;
            DataTable dt;
            try
            {
                var para1 = new[] {
                    DBhelper.SetParameter("@app_id", app_id),
                    DBhelper.SetParameter("@ContactID", ContactID),
                };

                string strQuery = "select * from crm_applicationcontact where app_id=@app_id and ContactID=@ContactID";
                dt = DBhelper.ExecuteDataset(strQuery, para1).Tables[0];
                int ApplicationcontactID = 0;
                if (dt.Rows.Count > 0)
                    ApplicationcontactID = int.Parse(dt.Rows[0]["ApplicationcontactID"].ToString());

                strQuery = "select DupwithContact from crm_contact where app_id=@app_id and ContactID=@ContactID";
                dt = DBhelper.ExecuteDataset(strQuery, para1).Tables[0];
                int CurrentContactID = 0;
                if (dt.Rows.Count > 0 && dt.Rows[0]["DupwithContact"].ToString() != "")
                    CurrentContactID = int.Parse(dt.Rows[0]["DupwithContact"].ToString());
                else
                    CurrentContactID = int.Parse(ContactID);

                var para = new[] {
                    DBhelper.SetParameter("@ApplicationcontactID", ApplicationcontactID),
                    DBhelper.SetParameter("@CurrentContactID", CurrentContactID),
                    DBhelper.SetParameter("@OriginalContactID", ContactID),
                    //DBhelper.SetParameter("@CreatedDate", CreatedDate),
                    DBhelper.SetParameter("@VedaFileNo", objVedaResponse.prim_individual_cross_reference_bureau_reference),
                    DBhelper.SetParameter("@OurReferNo", objVedaResponse.prim_individual_cross_reference_client_reference),
                    DBhelper.SetParameter("@XMLFile", XMLFile),
                    DBhelper.SetParameter("@CreatedBy", CreatedBy),
                };
                strQuery = "insert into crm_vedacreditcheckhistory(ApplicationcontactID,CurrentContactID,OriginalContactID,CreatedDate,XMLFile,VedafileNO,OurReferNo,CreatedBy) values(@ApplicationcontactID,@CurrentContactID,@OriginalContactID,NOW(),@XMLFile,@VedaFileNo,@OurReferNo,@CreatedBy);SELECT CreditcheckhistoryID FROM `crm_vedacreditcheckhistory` order by  CreditcheckhistoryID desc limit 1";
                dt = DBhelper.ExecuteDataset(strQuery, para).Tables[0];
                if (dt.Rows.Count > 0)
                    CreditcheckhistoryID = Convert.ToInt32(dt.Rows[0]["CreditcheckhistoryID"]);
            }
            catch (Exception ex)
            {
                throw ex;
            }
            return CreditcheckhistoryID;
        }

        public static void InsertVedaCreditData(int CreditcheckhistoryID, VedaResponse objVedaResponse)
        {
            try
            {
                var para = new[] {
                    DBhelper.SetParameter("@CreditcheckhistoryID", CreditcheckhistoryID),
                    DBhelper.SetParameter("@KnownIdentities", objVedaResponse.known_identities),
                    DBhelper.SetParameter("@AdverseOnFile", objVedaResponse.adverse_on_file),
                    DBhelper.SetParameter("@CreditInquiriesTwelveMonths", objVedaResponse.credit_enquiries_12),
                    DBhelper.SetParameter("@CreditInquiriesFiveYears", objVedaResponse.credit_enquiries_60),
                    DBhelper.SetParameter("@TotalValueOfOutstandingDefaults", objVedaResponse.total_value_of_outstanding_defaults),
                    DBhelper.SetParameter("@AuthorisedAgentsEnquiriesTwelveMonths", objVedaResponse.authorised_agents_enquiries_12),
                    DBhelper.SetParameter("@AgeOfFile", objVedaResponse.age_of_file),
                    DBhelper.SetParameter("@DirectorshipsCurrent", objVedaResponse.directorships_current),
                    DBhelper.SetParameter("@DirectorshipsPrevious", objVedaResponse.directorships_previous),
                    DBhelper.SetParameter("@Proprietorships", objVedaResponse.proprietorships),
                    DBhelper.SetParameter("@FileNotes", objVedaResponse.file_notes),
                    DBhelper.SetParameter("@AuthorisedAgentsEnquiriesFiveYears", objVedaResponse.authorised_agents_enquiries_60),
                    DBhelper.SetParameter("@VedaScore", objVedaResponse.veda_score),
                    DBhelper.SetParameter("@RelativeRisk", objVedaResponse.relative_risk),
                    DBhelper.SetParameter("@ApplicantOdds", objVedaResponse.applicant_odds),
                    DBhelper.SetParameter("@PopulationOdds", objVedaResponse.population_odds),
                    DBhelper.SetParameter("@ContributingFactorFiled1", objVedaResponse.contributing_factor_filed1),
                    DBhelper.SetParameter("@ContributingFactorFiled2", objVedaResponse.contributing_factor_filed2),
                    DBhelper.SetParameter("@ContributingFactorFiled3", objVedaResponse.contributing_factor_filed3),
                    DBhelper.SetParameter("@ContributingFactorFiled4", objVedaResponse.contributing_factor_filed4),
                    DBhelper.SetParameter("@ContributingFactorValue1", objVedaResponse.contributing_factor_value1),
                    DBhelper.SetParameter("@ContributingFactorValue2", objVedaResponse.contributing_factor_value2),
                    DBhelper.SetParameter("@ContributingFactorValue3", objVedaResponse.contributing_factor_value3),
                    DBhelper.SetParameter("@ContributingFactorValue4", objVedaResponse.contributing_factor_value4),
                };
                string strQuery = "insert into crm_vedacreditdata(CreditcheckhistoryID, KnownIdentities, AdverseOnFile, CreditInquiriesTwelveMonths, CreditInquiriesFiveYears, TotalValueOfOutstandingDefaults, AuthorisedAgentsEnquiriesTwelveMonths, AgeOfFile, DirectorshipsCurrent, DirectorshipsPrevious, Proprietorships, FileNotes, AuthorisedAgentsEnquiriesFiveYears, VedaScore, RelativeRisk, ApplicantOdds, PopulationOdds, ContributingFactorFiled1, ContributingFactorFiled2, ContributingFactorFiled3, ContributingFactorFiled4, ContributingFactorValue1, ContributingFactorValue2, ContributingFactorValue3, ContributingFactorValue4) values(@CreditcheckhistoryID, @KnownIdentities, @AdverseOnFile, @CreditInquiriesTwelveMonths, @CreditInquiriesFiveYears, @TotalValueOfOutstandingDefaults, @AuthorisedAgentsEnquiriesTwelveMonths, @AgeOfFile, @DirectorshipsCurrent, @DirectorshipsPrevious, @Proprietorships, @FileNotes, @AuthorisedAgentsEnquiriesFiveYears, @VedaScore, @RelativeRisk, @ApplicantOdds, @PopulationOdds, @ContributingFactorFiled1, @ContributingFactorFiled2, @ContributingFactorFiled3, @ContributingFactorFiled4, @ContributingFactorValue1, @ContributingFactorValue2, @ContributingFactorValue3, @ContributingFactorValue4)";
                DBhelper.ExecuteNonQuery(strQuery, para);
            }
            catch (Exception ex)
            {
                throw ex;
            }
        }
        public static string VedaCreditCheck(string app_id, string ContactID, string FirstGivenName, string FamilyName, string Gender, string DriversLicenceNumber, string DateofBirth, string StreetNumber, string StreetName, string Suburb, string State, string PostCode, string LoanAmount, string ClientReference, string CreatedDate, string CreatedBy, string VedaFileNo, int flagByCreditCheck)
        {
            string vedaXSLUrl = string.Empty;
            int CreditcheckhistoryID = 0;
            try
            {
                List<List<string>> lstDynamicParamters = new List<List<string>>();

                List<string> lstParamter = new List<string>();
                if (flagByCreditCheck == 0)
                {
                    lstParamter.Add("[[BCAservice-client-ref]]");
                    lstParamter.Add(ClientReference);
                    lstDynamicParamters.Add(lstParamter);
                    lstParamter = new List<string>();
                    lstParamter.Add("[[transaction_reference]]");
                    lstParamter.Add(DateTime.Now.ToString("yyyyMMddhhmmss"));
                    lstDynamicParamters.Add(lstParamter);
                    lstParamter = new List<string>();
                    lstParamter.Add("[[family_name]]");
                    lstParamter.Add(FamilyName);
                    lstDynamicParamters.Add(lstParamter);
                    lstParamter = new List<string>();
                    lstParamter.Add("[[first_given_name]]");
                    lstParamter.Add(FirstGivenName);
                    lstDynamicParamters.Add(lstParamter);
                    lstParamter = new List<string>();
                    lstParamter.Add("[[family_name]]");
                    lstParamter.Add(FamilyName);
                    lstDynamicParamters.Add(lstParamter);
                    lstParamter = new List<string>();
                    lstParamter.Add("[[street_number]]");
                    lstParamter.Add(StreetNumber);
                    lstDynamicParamters.Add(lstParamter);
                    lstParamter = new List<string>();
                    lstParamter.Add("[[street_name]]");
                    lstParamter.Add(StreetName);
                    lstDynamicParamters.Add(lstParamter);
                    lstParamter = new List<string>();
                    lstParamter.Add("[[suburb]]");
                    lstParamter.Add(Suburb);
                    lstDynamicParamters.Add(lstParamter);
                    lstParamter = new List<string>();
                    lstParamter.Add("[[state]]");
                    lstParamter.Add(State);
                    lstDynamicParamters.Add(lstParamter);
                    lstParamter = new List<string>();
                    lstParamter.Add("[[postcode]]");
                    lstParamter.Add(PostCode);
                    lstDynamicParamters.Add(lstParamter);
                    lstParamter = new List<string>();
                    lstParamter.Add("[[gender]]");
                    lstParamter.Add(Gender);
                    lstDynamicParamters.Add(lstParamter);
                    lstParamter = new List<string>();
                    lstParamter.Add("[[drivers_licence_number]]");
                    lstParamter.Add(DriversLicenceNumber);
                    lstDynamicParamters.Add(lstParamter);
                    lstParamter = new List<string>();
                    lstParamter.Add("[[date_of_birth]]");
                    lstParamter.Add(DateofBirth);
                    lstDynamicParamters.Add(lstParamter);
                    lstParamter = new List<string>();
                    lstParamter.Add("[[enquiry_amount]]");
                    lstParamter.Add(LoanAmount);
                    lstDynamicParamters.Add(lstParamter);
                }
                else
                {
                    lstParamter.Add("[[BCAservice-client-ref]]");
                    lstParamter.Add(ClientReference);
                    lstDynamicParamters.Add(lstParamter);
                    lstParamter = new List<string>();
                    lstParamter.Add("[[transaction_reference]]");
                    lstParamter.Add(DateTime.Now.ToString("yyyyMMddhhmmss"));
                    lstDynamicParamters.Add(lstParamter);
                    lstParamter = new List<string>();
                    lstParamter.Add("[[bureau_reference]]");
                    lstParamter.Add(VedaFileNo);
                    lstDynamicParamters.Add(lstParamter);
                }

                Stream output = CRMBiz.VedaService.processCreditRequest(lstDynamicParamters, flagByCreditCheck);
                Byte[] myData = CRMBiz.VedaService.ReadFully(output);
                string strres = System.Text.ASCIIEncoding.ASCII.GetString(myData);
                DataSet ResponseDS = CRMBiz.VedaService.ConvertXMLtoDataSet(strres);

                if (ResponseDS.Tables.Contains("error"))
                {
                    vedaXSLUrl = ResponseDS.Tables["error-description"].Rows[0]["error-description_Text"].ToString();
                }
                else if (ResponseDS.Tables["BCAerror"] != null)
                {
                    vedaXSLUrl = ResponseDS.Tables["BCAerror"].Rows[0]["BCAerror-description"].ToString();
                }
                else
                {
                    VedaResponse objVedaResponse = CRMBiz.VedaService.VedaResponseXMLParser(ResponseDS);
                    //store xml file in local directory
                    string XMLFile = System.Web.HttpContext.Current.Server.MapPath("~/") + "Pages\\Application\\VedaResponseData\\" + objVedaResponse.prim_individual_cross_reference_bureau_reference + "_" + DateTime.Now.ToString("yyyyMMddhhmmss") + ".xml";
                    File.WriteAllText(XMLFile, strres);
                    XDocument doc = XDocument.Load(XMLFile);
                    var proc = new XProcessingInstruction("xml-stylesheet", "type=\"text/xsl\" href=\"vedaxsl.xsl\"");
                    doc.Root.AddBeforeSelf(proc);
                    doc.Save(XMLFile);
                    vedaXSLUrl = objVedaResponse.prim_individual_cross_reference_bureau_reference + "_" + DateTime.Now.ToString("yyyyMMddhhmmss") + ".xml";

                    if (objVedaResponse != null)
                    {
                        //function of Credit Check History
                        CreditcheckhistoryID = UpdateVedaCreditCheck(app_id, ContactID, objVedaResponse, CreatedDate, CreatedBy, vedaXSLUrl);
                        InsertVedaCreditData(CreditcheckhistoryID, objVedaResponse);
                    }
                }
            }
            catch (Exception ex)
            {
                throw ex;
            }
            return vedaXSLUrl;
        }
    }
}
